*File paths below refer to raw HRS data available from HRS website 
global pendata04 "V:\LIBRARY\Xwave\PenWealthImp2004\built\stata"
global pendata10 "V:\LIBRARY\Xwave\PenWealthImp2010\built\stata"
global j04 "V:\LIBRARY\2004hrs\Final\Core\Built\stata"
global j06 "V:\LIBRARY\2006hrs\Final\Core\built\stata"
global j08 "V:\LIBRARY\2008hrs\Final\Core\built\stata"
global j10 "V:\LIBRARY\2010hrs\Final\Core\Built\stata"
global j12 "V:\LIBRARY\2012hrs\Final\Core\Built\stata"
global j14 "V:\LIBRARY\2014hrs\Final\Core\Built\stata"
global tracker "V:\LIBRARY\Tracker\Trk2016\Built\stata"
global randdata "V:\LIBRARY\Contrib\RAND\RandHrs\RandHrs2016ER2\stata"
*File path below is the replication data directory
global outdata ".../data"
set more off


*Extract from RAND 1992-2016 file 

use hhid pn ragender inw* r*iearn r*iwendy r*agey_b using $randdata/randhrs1992_2016v1, clear
tempfile randdat
save "`randdat'" /*save RAND data as tempfile*/

use $j04\H04J_R, clear

*Keep individuals with linked pensions in 2004 who are employed
merge 1:1 HHID PN using $pendata04\PWI04
keep if DBFlag04==1
keep if _merge==3
drop _merge

*Get income data for all waves from RAND file
rename HHID hhid
rename PN pn
merge 1:1 hhid pn using "`randdat'", keepusing(ragender inw* r*iearn r*iwendy r*agey_b)
rename hhid HHID
rename pn PN
keep if _merge==3
drop _merge

*Get R's age in 2004
merge 1:1 HHID PN using $tracker\TRK2016TR_R, keepusing(JAGE EXDEATHYR JWGTR KWGTR LWGTR MWGTR NWGTR)
keep if _merge==3
drop _merge
rename JAGE age2004

keep HHID PN JJ005M1 JJ005M2 JJ005M3 JJ005M4 JJ005M5 JJ018 JJ021 JJ020 ///
age2004 XAge_04x EXDEATHYR JWGTR KWGTR LWGTR MWGTR NWGTR ragender inw* r*iearn r*iwendy r*agey_b

*Employment status and earnings in 2006
merge 1:1 HHID PN using $j06\H06J_R, keepusing(KJ005M1 KJ005M2 KJ005M3 KJ005M4 KJ005M5 KJ018 KJ020 ///
KJ021 KJ188 KJ192 KJ189 KJ190 KJ172 KJ179 KJ578)
gen mergeok = (_merge==3) | (_merge==1 & EXDEATHYR!=.)
keep if mergeok==1
drop _merge mergeok
gen self_emp06= (KJ021==2)
gen ret_06= (KJ005M1==5 | KJ005M2==5 | KJ005M3==5 | KJ005M4==5 | KJ005M5==5)
gen nowkpay_06= KJ020==5
gen death_06 = inrange(EXDEATHYR,2004,2006)

gen self_emp_earn = .
replace KJ172=. if KJ172>=998
replace KJ179=. if KJ179>=98
replace self_emp_earn = KJ188*KJ172*KJ179 if KJ192==1 //hourly
replace self_emp_earn = KJ188*KJ179 if KJ192==2 //weekly
replace self_emp_earn = KJ188*KJ179/2 if KJ192==3 //bi-weekly
replace self_emp_earn = KJ188*KJ179/4 if KJ192==4 //monthly
replace self_emp_earn = KJ188*KJ179/2 if KJ192==5 //twice a month
replace self_emp_earn = KJ188 if KJ192==6 //annual
replace self_emp_earn = KJ188*KJ179*5 if KJ192==11 //daily
replace self_emp_earn =. if KJ188>999999998 //missing
gen self_emp_earn06= self_emp_earn
drop self_emp_earn

*Employment status and earnings in 2008
merge 1:1 HHID PN using $j08\H08J_R, keepusing(LJ005M1 LJ005M2 LJ005M3 LJ005M4 LJ018 LJ020 ///
LJ021 LJ188 LJ192 LJ189 LJ190 LJ172 LJ179)
gen mergeok = (_merge==3) | (_merge==1 & EXDEATHYR!=.)
keep if mergeok==1
drop _merge mergeok
gen self_emp08= (LJ021==2)
gen ret_08= (LJ005M1==5 | LJ005M2==5 | LJ005M3==5 | LJ005M4==5)
gen nowkpay_08= LJ020==5
gen death_08 = inrange(EXDEATHYR,2004,2008)

gen self_emp_earn = .
replace LJ172=. if LJ172>=998
replace LJ179=. if LJ179>=98
replace self_emp_earn = LJ188*LJ172*LJ179 if LJ192==1 //hourly
replace self_emp_earn = LJ188*LJ179 if LJ192==2 //weekly
replace self_emp_earn = LJ188*LJ179/2 if LJ192==3 //bi-weekly
replace self_emp_earn = LJ188*LJ179/4 if LJ192==4 //monthly
replace self_emp_earn = LJ188*LJ179/2 if LJ192==5 //twice a month
replace self_emp_earn = LJ188 if LJ192==6 //annual
replace self_emp_earn = LJ188*LJ179*5 if LJ192==11 //daily
replace self_emp_earn =. if LJ188>999999998 //missing
gen self_emp_earn08= self_emp_earn
drop self_emp_earn

*Employment status and earnings in 2010
merge 1:1 HHID PN using $j10\H10J_R, keepusing(MJ005M1 MJ005M2 MJ005M3 MJ005M4 MJ005M5 MJ018 MJ020 ///
MJ021 MJ188 MJ192 MJ189 MJ190 MJ172 MJ179)
gen mergeok = (_merge==3) | (_merge==1 & EXDEATHYR!=.)
keep if mergeok==1
drop _merge mergeok
gen self_emp10= (MJ021==2)
gen ret_10= (MJ005M1==5 | MJ005M2==5 | MJ005M3==5 | MJ005M4==5 | MJ005M5==5)
gen nowkpay_10= MJ020==5
gen death_10 = inrange(EXDEATHYR,2004,2010)

gen self_emp_earn = .
replace MJ172=. if MJ172>=998
replace MJ179=. if MJ179>=98
replace self_emp_earn = MJ188*MJ172*MJ179 if MJ192==1 //hourly
replace self_emp_earn = MJ188*MJ179 if MJ192==2 //weekly
replace self_emp_earn = MJ188*MJ179/2 if MJ192==3 //bi-weekly
replace self_emp_earn = MJ188*MJ179/4 if MJ192==4 //monthly
replace self_emp_earn = MJ188*MJ179/2 if MJ192==5 //twice a month
replace self_emp_earn = MJ188 if MJ192==6 //annual
replace self_emp_earn = MJ188*MJ179*5 if MJ192==11 //daily
replace self_emp_earn =. if MJ188>999999998 //missing
gen self_emp_earn10= self_emp_earn
drop self_emp_earn

*Employment status and earnings in 2012
merge 1:1 HHID PN using $j12\H12J_R, keepusing(NJ005M1 NJ005M2 NJ005M3 NJ005M4 NJ005M5 NJ018 NJ020 ///
NJ021 NJ188 NJ192 NJ189 NJ190 NJ172 NJ179)
gen mergeok = (_merge==3) | (_merge==1 & EXDEATHYR!=.)
keep if mergeok==1
drop _merge mergeok
gen self_emp12= (NJ021==2)
gen ret_12= (NJ005M1==5 | NJ005M2==5 | NJ005M3==5 | NJ005M4==5 | NJ005M5==5)
gen nowkpay_12= NJ020==5
gen death_12 = inrange(EXDEATHYR,2004,2012)

gen self_emp_earn = .
replace NJ172=. if NJ172>=998
replace NJ179=. if NJ179>=98
replace self_emp_earn = NJ188*NJ172*NJ179 if NJ192==1 //hourly
replace self_emp_earn = NJ188*NJ179 if NJ192==2 //weekly
replace self_emp_earn = NJ188*NJ179/2 if NJ192==3 //bi-weekly
replace self_emp_earn = NJ188*NJ179/4 if NJ192==4 //monthly
replace self_emp_earn = NJ188*NJ179/2 if NJ192==5 //twice a month
replace self_emp_earn = NJ188 if NJ192==6 //annual
replace self_emp_earn = NJ188*NJ179*5 if NJ192==11 //daily
replace self_emp_earn =. if NJ188>999999998 //missing
gen self_emp_earn12= self_emp_earn
drop self_emp_earn

*Employment status and earnings in 2014
merge 1:1 HHID PN using $j14\H14J_R, keepusing(OJ005M1 OJ005M2 OJ005M3 OJ005M4 OJ005M5 OJ018 OJ020 ///
OJ021 OJ188 OJ192 OJ189 OJ190 OJ172 OJ179)
gen mergeok = (_merge==3) | (_merge==1 & EXDEATHYR!=.)
keep if mergeok==1
drop _merge mergeok
gen self_emp14= (OJ021==2)
gen ret_14= (OJ005M1==5 | OJ005M2==5 | OJ005M3==5 | OJ005M4==5 | OJ005M5==5)
gen nowkpay_14= OJ020==5
gen death_14 = inrange(EXDEATHYR,2004,2014)

gen self_emp_earn = .
replace OJ172=. if OJ172>=998
replace OJ179=. if OJ179>=98
replace self_emp_earn = OJ188*OJ172*OJ179 if OJ192==1 //hourly
replace self_emp_earn = OJ188*OJ179 if OJ192==2 //weekly
replace self_emp_earn = OJ188*OJ179/2 if OJ192==3 //bi-weekly
replace self_emp_earn = OJ188*OJ179/4 if OJ192==4 //monthly
replace self_emp_earn = OJ188*OJ179/2 if OJ192==5 //twice a month
replace self_emp_earn = OJ188 if OJ192==6 //annual
replace self_emp_earn = OJ188*OJ179*5 if OJ192==11 //daily
replace self_emp_earn =. if OJ188>999999998 //missing
gen self_emp_earn14= self_emp_earn
drop self_emp_earn

*Retirement definition closest to admin data
gen noadearn06= (r8iearn==0 | (r8iearn<=self_emp_earn06 & self_emp_earn06<.))
gen noadearn08= (r9iearn==0 | (r9iearn<=self_emp_earn08 & self_emp_earn08<.))
gen noadearn10= (r10iearn==0 | (r10iearn<=self_emp_earn10 & self_emp_earn10<.))
gen noadearn12= (r11iearn==0 | (r11iearn<=self_emp_earn12 & self_emp_earn12<.))
gen noadearn14= (r12iearn==0 | (r12iearn<=self_emp_earn14 & self_emp_earn14<.))

gen adminret_06= (noadearn06==1 & noadearn08==1 & noadearn10==1 & noadearn12==1 & noadearn14==1) 
gen adminret_08= (noadearn08==1 & noadearn10==1 & noadearn12==1 & noadearn14==1) 
gen adminret_10= (noadearn10==1 & noadearn12==1 & noadearn14==1) 
gen adminret_12= (noadearn12==1 & noadearn14==1) 
gen adminret_14= (noadearn14==1) 

*Longitudinally consistent self-report
gen retlc_06 = (ret_06==1 & ret_08==1 & ret_10==1 & ret_12==1 & ret_14==1)
gen retlc_08 = (ret_08==1 & ret_10==1 & ret_12==1 & ret_14==1)
gen retlc_10 = (ret_10==1 & ret_12==1 & ret_14==1)
gen retlc_12 = (ret_12==1 & ret_14==1)
gen retlc_14 = ret_14

*Longitudinally consistent self-report of no work for pay (reduces issues about measurement error in earnings)
gen nowkpaylc_06 = (nowkpay_06==1 & nowkpay_08==1 & nowkpay_10==1 & nowkpay_12==1 & nowkpay_14==1)
gen nowkpaylc_08 = (nowkpay_08==1 & nowkpay_10==1 & nowkpay_12==1 & nowkpay_14==1)
gen nowkpaylc_10 = (nowkpay_10==1 & nowkpay_12==1 & nowkpay_14==1)
gen nowkpaylc_12 = (nowkpay_12==1 & nowkpay_14==1)
gen nowkpaylc_14 = nowkpay_14==1

*Binaries for receipt of self-employment income
gen any_slfearn06= (self_emp_earn06>0 & self_emp_earn06<.)
gen any_slfearn08= (self_emp_earn08>0 & self_emp_earn08<.)
gen any_slfearn10= (self_emp_earn10>0 & self_emp_earn10<.)
gen any_slfearn12= (self_emp_earn12>0 & self_emp_earn12<.)
gen any_slfearn14= (self_emp_earn14>0 & self_emp_earn14<.)


***Summarize rates by year
gen age_bin=.
replace age_bin=1 if (inrange(age2004,50,55)) 
replace age_bin=2 if (inrange(age2004,56,64))
replace age_bin=3 if (inrange(age2004,65,70))

tabstat adminret_06 adminret_08 adminret_10 adminret_12 adminret_14 [aw=JWGTR], by(age_bin)
tabstat nowkpaylc_06 nowkpaylc_08 nowkpaylc_10 nowkpaylc_12 nowkpaylc_14 [aw=JWGTR], by(age_bin)
tabstat retlc_06 retlc_08 retlc_10 retlc_12 retlc_14 [aw=JWGTR], by(age_bin)
tabstat any_slfearn06 any_slfearn08 any_slfearn10 any_slfearn12 any_slfearn14 [aw=JWGTR], by(age_bin)


*Export R rates by age bin to matrix 
matrix R_rates = J(5,7,.)
local j = 1
forvalues y = 6(2)14{
		if `y'==6{
			local var = "retlc_06"
		}
		else if `y' == 8{
			local var = "retlc_08"
		}
		else if `y' == 10{
			local var = "retlc_10"
		}
		else if `y' == 12{
			local var = "retlc_12"
		}
		else if `y' == 14{
			local var = "retlc_14"
		}
		matrix R_rates[`j',1]=2000+`y'
		summ `var' if age_bin==1 //[aw=JWGTR]
		matrix R_rates[`j',2]=r(mean)
		matrix R_rates[`j',3]=r(sd)/sqrt(r(N))
		summ `var' if age_bin==2 //[aw=JWGTR]
		matrix R_rates[`j',4]=r(mean)
		matrix R_rates[`j',5]=r(sd)/sqrt(r(N))
		summ `var' if age_bin==3 //[aw=JWGTR]
		matrix R_rates[`j',6]=r(mean)	
		matrix R_rates[`j',7]=r(sd)/sqrt(r(N))	
		local j = `j'+1
}

preserve
	clear
	svmat R_rates
	rename R_rates1 year
	rename R_rates2 _50_55_avg
	rename R_rates3 _50_55_se
	rename R_rates4 _56_64_avg
	rename R_rates5 _56_64_se	
	rename R_rates6 _65_70_avg
	rename R_rates7 _65_70_se	
	export delimited "$outdata/R_rates_allHRS04.csv", replace
restore

